package db;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

public class DbUtil {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/demo6?useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    
    private static DbUtil dbUtil;
    static {
        dbUtil = new DbUtil(DB_URL, USER, PASSWORD);
    }
    
    public static DbUtil getInstance() {
        return dbUtil;
    }
    
    public static void main(String[] args) {
        System.out.println(DbUtil.getInstance().getConn());
    }


    private Connection conn;

    private DbUtil(String url, String username, String password){
        try {
          Class.forName("com.mysql.jdbc.Driver");
          conn = DriverManager.getConnection(url, username, password);
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      } catch (SQLException e) {
          e.printStackTrace();
      }
    }

    
    public Connection initConnection() {
        return conn;
    }
    
    public void closeConnection(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void closeStatement(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> queryList(String sql, Object[] o){
        List<Map<String, Object>> list = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            if (o != null) {
                for(int i = 1; i <= o.length; i++) {
                    ps.setObject(i, o[i-1]);
                }
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            list = new ArrayList<Map<String, Object>>();
            while(rs.next()) {
                Map<String, Object> m = new HashMap<>();
                for(int i = 1; i <= rsmd.getColumnCount(); i++) {
                    m.put(rsmd.getColumnName(i), rs.getObject(i));
                }
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(ps);
        }
        return list;
    }
    
    public  Map<String, Object> queryForMap(String sql, Object[] o){
        List<Map<String, Object>> list = queryList(sql, o);
        if (list != null && list.size() > 0) return list.get(0);
        else return null;
    }
    
    public  <T> T queryObject(String sql, Object[] o, Class<T> c){
        List<T> list = queryList(sql, o, c);
        if (list != null && list.size() > 0) return list.get(0);
        else return null;
    }
    
    public  <T> List<T> queryList(String sql, Object[] o, Class<T> c){
        List<T> list = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            if (o != null) {
                for(int i = 1; i <= o.length; i++) {
                    ps.setObject(i, o[i-1]);
                }
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            list = new ArrayList<T>();
            while(rs.next()) {
                T t = c.getDeclaredConstructor().newInstance();
                for(int i = 1; i <= rsmd.getColumnCount(); i++) {
//                    String columnName = rsmd.getColumnName(i);
                    String columnName = rsmd.getColumnLabel(i);
                   
                    String property = "";
                    boolean upcase = false;
                    for(int k = 0; k < columnName.length(); k++) {
                        if (columnName.charAt(k) == '_') {
                            upcase = true;
                            continue;
                        }
                        if (upcase) {
                            property += (""+columnName.charAt(k)).toUpperCase();
                        }else {
                            property += columnName.charAt(k);
                        }
                        upcase = false;
                    }
                    String methodName = "set" + property.substring(0, 1).toUpperCase()
                            + property.substring(1, property.length());
                    try {
                        Method method = c.getDeclaredMethod(methodName, 
                                c.getDeclaredField(property).getType());
                        Object obj = rs.getObject(i);
                        method.invoke(t, obj);
                    } catch (Exception e) {
                        
                    }
                }
                list.add(t);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (SecurityException e) {
            e.printStackTrace();
        } finally {
            closeStatement(ps);
        }
        return list;
    }
    
    public int execute(String sql, Object[] o) {
        PreparedStatement ps = null;
        int result = -1;
        try {
            ps = conn.prepareStatement(sql);
            if (o != null) {
                for(int i = 1; i <= o.length; i++) {
                    ps.setObject(i, o[i-1]);
                }
            }
            result = ps.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(ps);
        }
        return result;
    }

    public Connection getConn(){
        return  conn;
    }
}